﻿//using Dapper;
//using Sgr.Data;
//using Sgr.EventBus.RelayBoxs.Domain;

//namespace Sgr.EventBus.RelayBoxs.Storage
//{
//    public class OutboxMessageStorage : IOutboxMessageStorage
//    {
//        private readonly IDbTransactionContext _transactionContext;

//        public OutboxMessageStorage(IDbTransactionContext transactionContext)
//        {
//            _transactionContext = transactionContext ?? throw new ArgumentNullException(nameof(transactionContext));
//        }

//        public async Task InsertAsync(OutboxMessage message, CancellationToken cancellationToken = default)
//        {
//            const string sql = @"
//INSERT INTO sgr_outbox_message (
//    sqr_id, sqr_type, sqr_message_id, sqr_topic, sqr_payload,
//    sqr_occurred_on_utc, sqr_next_on_utc, sqr_dispatched_on_utc,
//    sqr_status, sqr_retry_count, sqr_last_error
//) VALUES (
//    @Id, @MessageType, @MessageId, @MessageTopic, @Payload,
//    @OccurredOnUtc, @NextOnUtc, @DispatchedOnUtc,
//    @Status, @RetryCount, @LastError
//);";

//            var parameters = new DynamicParameters();
//            parameters.Add("@Id", message.Id);
//            parameters.Add("@MessageType", message.MessageType);
//            parameters.Add("@MessageId", message.MessageId);
//            parameters.Add("@MessageTopic", message.MessageTopic);
//            parameters.Add("@Payload", message.Payload);
//            parameters.Add("@OccurredOnUtc", message.OccurredOnUtc);
//            parameters.Add("@NextOnUtc", message.NextOnUtc);
//            parameters.Add("@DispatchedOnUtc", message.DispatchedOnUtc);
//            parameters.Add("@Status", message.Status);
//            parameters.Add("@RetryCount", message.RetryCount);
//            parameters.Add("@LastError", message.LastError);

//            var command = new CommandDefinition(sql, parameters, _transactionContext.Transaction, cancellationToken: cancellationToken);
//            await _transactionContext.Connection.ExecuteAsync(command);
//        }

//public async Task<IEnumerable<OutboxMessage>> GetPendingMessagesAsync(int limit, CancellationToken cancellationToken = default)
//{
//    var limitClause = OutboxMessageSqlBuilder.GetLimitClause(_transactionContext.DbType, limit);

//    string sql = $@"
//SELECT
//    sqr_id AS Id,
//    sqr_type AS MessageType,
//    sqr_message_id AS MessageId,
//    sqr_topic AS MessageTopic,
//    sqr_payload AS Payload,
//    sqr_occurred_on_utc AS OccurredOnUtc,
//    sqr_next_on_utc AS NextOnUtc,
//    sqr_dispatched_on_utc AS DispatchedOnUtc,
//    sqr_status AS Status,
//    sqr_retry_count AS RetryCount,
//    sqr_last_error AS LastError
//FROM sgr_outbox_message
//WHERE sqr_status = 0 AND sqr_next_on_utc <= @NowUtc
//ORDER BY sqr_next_on_utc
//{limitClause}";

//    var command = new CommandDefinition(
//        sql,
//        new { NowUtc = DateTime.UtcNow, Limit = limit },
//        _transactionContext.Transaction,
//        cancellationToken: cancellationToken
//    );

//            return await _transactionContext.Connection.QueryAsync<OutboxMessage>(command);
//        }

//        public async Task MarkDispatchedAsync(IEnumerable<long> messageIds, CancellationToken cancellationToken = default)
//        {
//            var ids = messageIds?.ToList();
//            if (ids == null || ids.Count == 0)
//                return;

//            const int batchThreshold = 600;

//            var nowUtc = DateTime.UtcNow;

//            if (ids.Count > batchThreshold)
//            {
//                // 分批处理
//                var batches = ids
//                    .Select((id, index) => new { id, index })
//                    .GroupBy(x => x.index / batchThreshold)
//                    .Select(g => g.Select(x => x.id).ToList());
//                foreach (var batch in batches)
//                {
//                    await ExecuteMarkDispatchedBatch(batch, nowUtc, cancellationToken);
//                }
//            }
//            else
//            {
//                await ExecuteMarkDispatchedBatch(ids, nowUtc, cancellationToken);
//            }
//        }

//        private async Task ExecuteMarkDispatchedBatch(IList<long> messageIds, DateTime nowUtc, CancellationToken cancellationToken)
//        {
//            const string sql = @"
//UPDATE sgr_outbox_message
//SET sqr_dispatched_on_utc = @NowUtc, sqr_status = 1, sqr_next_on_utc = @NowUtc
//WHERE sqr_id IN @Ids AND sqr_status != 1;";

//            var command = new CommandDefinition(
//                sql,
//                new { Ids = messageIds, NowUtc = nowUtc },
//                _transactionContext.Transaction,
//                cancellationToken: cancellationToken
//            );

//            await _transactionContext.Connection.ExecuteAsync(command);
//        }

//        public async Task MarkFailedAsync(long messageId, string lastError = "", CancellationToken cancellationToken = default)
//        {
//            const string sql = @"
//UPDATE sgr_outbox_message
//SET sqr_status = 2, sqr_retry_count = sqr_retry_count + 1, sqr_last_error = @LastError, sqr_next_on_utc = @NowUtc
//WHERE sqr_id = @MessageId AND sqr_status = 0;";

//            var command = new CommandDefinition(
//                sql,
//                new { MessageId = messageId, LastError = lastError, NowUtc = DateTime.UtcNow },
//                _transactionContext.Transaction,
//                cancellationToken: cancellationToken
//            );

//            await _transactionContext.Connection.ExecuteAsync(command);
//        }

//        public async Task MarkRetryAsync(long messageId, DateTime nextOnUtc, string lastError = "", CancellationToken cancellationToken = default)
//        {
//            const string sql = @"
//UPDATE sgr_outbox_message
//SET sqr_retry_count = sqr_retry_count + 1, sqr_next_on_utc = @NextOnUtc, sqr_last_error = @LastError
//WHERE sqr_id = @MessageId AND sqr_status = 0;";

//            var command = new CommandDefinition(
//                sql,
//                new { MessageId = messageId, NextOnUtc = nextOnUtc, LastError = lastError },
//                _transactionContext.Transaction,
//                cancellationToken: cancellationToken
//            );

//            await _transactionContext.Connection.ExecuteAsync(command);
//        }

//        /// <summary>
//        /// 清理一批已成功投递的消息（状态为 1，投递时间早于 olderThanUtc），最多删除 max 条，返回实际删除条数
//        /// </summary>
//        public async Task<int> CleanupDispatchedMessagesAsync(DateTime olderThanUtc, int max, CancellationToken cancellationToken = default)
//        {
//            // 调用示例
//            //int maxPerBatch = 1000;
//            //int totalDeleted = 0;
//            //int deleted;

//            //DateTime cutoff = DateTime.UtcNow.AddDays(-30); // 例如清理30天前的

//            //do
//            //{
//            //    deleted = await outboxStorage.CleanupDispatchedMessagesAsync(cutoff, maxPerBatch, cancellationToken);
//            //    totalDeleted += deleted;
//            //    // 记录日志、监控、或控制事务等
//            //} while (deleted > 0);

//            return await CleanupByStatusAndDateAsync(
//                status: OutboxStatus.Dispatched,
//                olderThanUtc: olderThanUtc,
//                max: max,
//                cancellationToken: cancellationToken
//            );
//        }

//        /// <summary>
//        /// 清理一批已失败的消息（状态为 2，失败时间早于 olderThanUtc），最多删除 max 条，返回实际删除条数
//        /// </summary>
//        public async Task<int> CleanupFailedMessagesAsync(DateTime olderThanUtc, int max, CancellationToken cancellationToken = default)
//        {
//            if (max <= 0)
//                return 0;

//            if (max > 2000)
//                max = 2000; // 限制单次删除最大值，防止误操作删除过多

//            return await CleanupByStatusAndDateAsync(
//                status: OutboxStatus.Failed,
//                olderThanUtc: olderThanUtc,
//                max: max,
//                cancellationToken: cancellationToken
//            );
//        }

//        /// <summary>
//        /// 通用方法：执行单次分页删除（按状态 + 日期字段），返回实际删除的行数
//        /// </summary>
//        private async Task<int> CleanupByStatusAndDateAsync(OutboxStatus status, DateTime olderThanUtc, int max, CancellationToken cancellationToken)
//        {
//            const string deleteSqlTemplate = @"
//DELETE FROM sgr_outbox_message
//WHERE sqr_id IN (
//    SELECT sqr_id FROM sgr_outbox_message
//    WHERE sqr_status = @Status AND sqr_next_on_utc < @OlderThanUtc
//    ORDER BY sqr_next_on_utc
//    {0}
//);";

//            // 使用 OutboxMessageSqlBuilder 生成兼容的 LIMIT 子句，例如 LIMIT @max 或 FETCH FIRST @max ROWS ONLY
//            var limitClause = OutboxMessageSqlBuilder.GetLimitClause(_transactionContext.DbType, max);

//            // 格式化 SQL：将 {0} 替换为 dateField，{1} 替换为 limitClause
//            var formattedSql = string.Format(deleteSqlTemplate, limitClause);

//            var command = new CommandDefinition(
//                formattedSql,
//                new { Status = status, OlderThanUtc = olderThanUtc, Max = max },
//                _transactionContext.Transaction,
//                cancellationToken: cancellationToken
//            );

//            var affectedRows = await _transactionContext.Connection.ExecuteAsync(command);

//            return affectedRows;
//        }
//    }
//}